<?php
class Project_model extends Model {
	function __construct(){
		parent::Model();
	}
	
	function getProjects($pid=0)
	{
		$this -> db -> select() -> from('projects') -> order_by('name');
		if($pid)
			$this -> db -> where('id', $pid);
		$result = $this -> db -> get();
		if($result -> num_rows() == 0)
			return false;
			
		foreach($result -> result_array() as $row)
		{
			$arr[] = $row;
		}
		
		return $arr;
	}
	
	function getProjectUsers($pid)
	{
		$this -> db -> select('uid') -> from('user_to_project') -> where('pid', $pid);
		$result = $this -> db -> get();
		
		if($result -> num_rows() == 0)
			return array();
		
		foreach($result -> result_array() as $row)
		{
//			print_r($row);
			$arr[] = $row['uid'];
		}
		
		return $arr;
	}
	
	function manageProject($arr, $pid, $users)
	{
		if($pid) // updating
		{
			$this -> db -> where('pid', $pid) -> delete('user_to_project');
//			echo $this -> db -> last_query();
			$this -> db -> where('id', $pid) -> update('projects', $arr);
//			echo $this -> db -> last_query();
		}
		else // insert
		{
			$this -> db -> insert('projects', $arr);
			$pid = $this -> db -> insert_id();
		}
		
		if(is_array($users))
		{
			foreach($users as $user)
			{
				$this -> db -> insert('user_to_project', array("uid" => $user, "pid" => $pid));
			}
		}
	}
	
	function getUsersProjects($uid)
	{
		$this -> db -> select('UP.pid, P.name') -> from('user_to_project UP') -> join('projects P', 'P.id = UP.pid') -> where('uid', $uid);
		$result = $this -> db -> get();
		
		if($result -> num_rows() == 0)
			return false;
		
		foreach($result -> result_array() as $row)
		{
			$arr[] = $row;
		}
		
		return $arr;
	}
	
	function getUserHoursByProject($pid, $uid, $start)
	{
		$s = date("Y-m-d", $start);
		$e = date("Y-m-d", strtotime("+1 week", $start));
		
		$this -> db -> select('UNIX_TIMESTAMP(pvm) AS pvm, amount') -> from('hours');
		$this -> db -> where('uid', $uid) -> where('pid', $pid) -> where("pvm BETWEEN '$s' AND '$e'");
		$this -> db -> order_by('pvm', 'asc');
		
		$result = $this -> db -> get();
		if($result -> num_rows() == 0)
			return false;
		foreach($result -> result_array() as $row)
		{
			$arr[$row['pvm']] = $row['amount'];
		}
		
		return $arr;
	}
	
	function manageHours($uid, $pid, $time, $amount)
	{
		$this -> db -> select("id") -> from('hours');
		$this -> db -> where('uid', $uid) -> where('pid', $pid) -> where("UNIX_TIMESTAMP(pvm) = $time");
		
		$result = $this -> db -> get();
		if($result -> num_rows() == 0)
		{
			$this -> db -> insert('hours', array("uid" => $uid, "pid" => $pid, "pvm" => date("Y-m-d", $time), "amount" => $amount));
		}
		else
		{
			$this -> db -> where('uid', $uid) -> where('pid', $pid) -> where("UNIX_TIMESTAMP(pvm) = $time");
			$this -> db -> update('hours', array('amount' => $amount));
		}
		
	}
	
	/**
	 * Querying name for the user and sum amount of hours done within certain timeperiod
	 *
	 * @param timestamp $start
	 * @param timestamp $end
	 */
	function getSumHoursPerUser($start, $end)
	{
		$hours = $this -> db -> dbprefix('hours');
		$users = $this -> db -> dbprefix('users');
		$result = $this -> db -> query("	SELECT U.firstname, U.surname, SUM(H.amount) AS amount
											FROM $users U
											LEFT JOIN $hours H ON H.uid = U.id
											WHERE UNIX_TIMESTAMP(H.pvm) BETWEEN $start AND $end
											GROUP BY U.id
											ORDER BY U.surname, U.firstname");
		if($result -> num_rows() == 0)
			return false;
		foreach($result -> result_array() as $row)
		{
			$arr[]=array("name" => $row['firstname'] ." ". $row['surname'], "amount" => $row['amount']);
		}
		
		return $arr;
	}
	
	function getSumHoursPerProject($start, $end)
	{
		$this -> db -> select('P.name, SUM(H.amount) AS amount', false);
		$this -> db -> from('projects P');
		$this -> db -> join('hours H', 'P.id = H.pid');
		$this -> db -> where("UNIX_TIMESTAMP(H.pvm) BETWEEN $start AND $end", NULL, false);
		$this -> db -> group_by('P.id');
		$this -> db -> order_by('P.name');
		
		$result = $this -> db -> get();
		
		if($result -> num_rows() == 0)
			return false;
		foreach($result -> result_array() as $row)
		{
			$arr[]=array("name" => $row['name'], "amount" => $row['amount']);
		}
		
		return $arr;
	}
}